Databases store large amount of data that is processed in response to database queries. Typically databases generate an execution plan for the queries. The execution plan comprises various operators and order of execution of these operators to process the database query. A database query optimizer typically evaluates several execution plans to select a particular query plan for execution. The query optimizer selects the query plan that the optimizer determines as the best query plan for executing the database query. An execution plan is also referred to herein as a physical plan, a query plan, or a plan.
Generating the optimal query plan is a complex process that can take significant amount of time and resources depending on the complexity of the query being optimized. The number of execution plans that are possible for a complex query can be very large. An optimizer attempts to find the optimal query plan from this large number of query plans. However, finding the optimal plan can be a complex and resource consuming process. Query optimizers typically use heuristics to find an execution plan fast even if the execution plan may not be optimal. However, as a result, optimizers can make incorrect decisions while generating the execution plan for a query. If the optimizer makes incorrect decisions while generating the execution plan for a query, the execution of the query can be significantly slow compared to an optimal plan.